package com.qihoo.finance.lcs.sqltools;

import com.google.common.base.Joiner;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.junit.Assert;
import org.junit.Test;

import java.util.*;

public class Simple {

    @Test
    public void test_parse_relation() {

        String sql = "select \n" +
                "l.third_code as \"合作方\",\n" +
                "case when l.date_inst >= to_date('2018-10-26', 'yyyy-mm-dd') then '新模式' else '老模式' end as 模式,\n" +
                "to_char(repay.tran_day,'yyyy-mm-dd') as \"还款完成日期\",\n" +
                "case WHEN l.rpy_type = '04' THEN '新等额本金'\n" +
                "when l.rpy_type = '00' then '等额本金'\n" +
                "when l.rpy_type = '02' then '先息后本'\n" +
                "else '分期' end 业务模式,\n" +
                "case when l.rpy_type = '03' then '等本等息'\n" +
                "when l.rpy_type = '02' then '先息后本'\n" +
                "when l.rpy_type = '00' then '等额本金' end AS \"交易类型\",\n" +
                "CASE WHEN pd.product_code = '360JIETIAO' and pd.cust_seg = 'X36' THEN '小额贷' WHEN pd.product_code = '360JIETIAO' THEN '360借条'\n" +
                "WHEN pd.product_code = '360YINGJI' THEN '小额贷' \n" +
                "WHEN pd.product_code = '360SME' THEN '360小微贷'\n" +
                "WHEN pd.product_code = '360BIG' THEN '尊享大额贷' END AS 产品类型,\n" +
                "'=\"'||l.cust_no||'\"' AS \"客户号\",\n" +
                "'=\"'||l.loan_no||'\"' AS \"借据号\",\n" +
                "'=\"'||l.loan_req_no||'\"' 借款流水号,\n" +
                "'按期代偿' as \"代偿标识\",\n" +
                "repay.tran_prin 本金,\n" +
                "repay.tran_int \"分期手续费/利息\",\n" +
                "trunc(case when l.rpy_type = '03' then repay.tran_int-repay.tran_deduct\n" +
                "when l.rpy_type = '02' then repay.tran_int-repay.tran_deduct\n" +
                "when l.rpy_type = '00' then repay.tran_int + repay.tran_f1 - repay.tran_deduct end, 2) AS \"实收分期手续费/实收利息\",\n" +
                "repay.tran_deduct 活动金,\n" +
                "repay.tran_f6 违约金,\n" +
                "repay.tran_oi 罚息,\n" +
                "repay.tran_f3 复利,\n" +
                "repay.tran_f5 红线减免,\n" +
                "0 长期借款福利\n" +
                "from(\n" +
                "select d.loan_no,sum(d.tran_amt) tran_amt,d.date_created tran_day,\n" +
                "sum(\n" +
                "case when d.tran_code in ('MPR','RPR') then d.tran_amt else 0 end\n" +
                ") tran_prin,\n" +
                "sum(\n" +
                "case when d.tran_code in ('MIN','RIN') then d.tran_amt else 0 end\n" +
                ") tran_int,\n" +
                "sum(\n" +
                "case when d.tran_code in ('ROI','MOI') then d.tran_amt else 0 end\n" +
                ") tran_oi,\n" +
                "sum(\n" +
                "case when d.tran_code in ('MF6','RF6') then d.tran_amt else 0 end\n" +
                ") tran_f6,\n" +
                "sum(\n" +
                "case when d.tran_code in ('MF5','RF5') then d.tran_amt else 0 end\n" +
                ") tran_f5,\n" +
                "sum(\n" +
                "case when d.tran_code in ('MDC','RDC') then d.tran_amt else 0 end\n" +
                ") tran_deduct,\n" +
                "sum(\n" +
                "case when d.tran_code in ('MF3','RF3') then d.tran_amt else 0 end\n" +
                ") tran_f3,\n" +
                "sum(\n" +
                "case when d.tran_code in ('MF1','RF1') then d.tran_amt else 0 end\n" +
                ") tran_f1\n" +
                "from lcs_third.tr_cust_tran_detail_view d\n" +
                "left join cps.intf_ncf_repay_detail ncf on d.loan_no = ncf.loan_no and d.plan_no = ncf.plan_no\n" +
                "left join lcs.ln_loan_view l on d.loan_no = l.loan_no \n" +
                "where d.tran_type<>'DB'\n" +
                "and d.date_created>=current_date - 4\n" +
                "and d.date_created<current_date\n" +
                "and l.third_code='THIRD_NCF'\n" +
                "and ncf.repay_type='02'\n" +
                "AND ncf.repay_mode='02'\n" +
                "group by d.loan_no,tran_day\n" +
                ") repay left join lcs.ln_loan_view l on repay.loan_no = l.loan_no\n" +
                "left join lcs.pd_product_def pd on l.sub_product_ver = pd.sub_product_ver and l.sub_product_code = pd.sub_product_code\n" +
                "\n" +
                "union all \n" +
                "\n" +
                "select  \n" +
                "l.third_code as \"合作方\",\n" +
                "'新模式' as 模式,\n" +
                "to_char(repay.tran_day,'yyyy-mm-dd') as \"还款完成日期\",\n" +
                "case WHEN l.rpy_type = '04' THEN '新等额本金'\n" +
                "when l.rpy_type = '00' then '等额本金'\n" +
                "when l.rpy_type = '02' then '先息后本'\n" +
                "else '分期' end 业务模式,\n" +
                "case when l.rpy_type = '03' then '等本等息'\n" +
                "when l.rpy_type = '02' then '先息后本'\n" +
                "when l.rpy_type = '00' then '等额本金' end AS \"交易类型\",\n" +
                "CASE WHEN pd.product_code = '360JIETIAO' and pd.cust_seg = 'X36' THEN '小额贷' WHEN pd.product_code = '360JIETIAO' THEN '360借条'\n" +
                "WHEN pd.product_code = '360YINGJI' THEN '小额贷' \n" +
                "WHEN pd.product_code = '360BIG' THEN '尊享大额贷' END AS 产品类型,\n" +
                "'=\"'||l.cust_no||'\"' AS \"客户号\",\n" +
                "'=\"'||l.loan_no||'\"' AS \"借据号\",\n" +
                "'=\"'||l.loan_req_no||'\"' 借款流水号,\n" +
                "'按期代偿' as \"代偿标识\",\n" +
                "repay.tran_prin 本金,\n" +
                "repay.tran_int \"分期手续费/利息\",\n" +
                "trunc(case when l.rpy_type = '03' then repay.tran_int-repay.tran_deduct\n" +
                "when l.rpy_type = '02' then repay.tran_int-repay.tran_deduct\n" +
                "when l.rpy_type = '00' then repay.tran_int + repay.tran_f1 - repay.tran_deduct end, 2) AS \"实收分期手续费/实收利息\",\n" +
                "repay.tran_deduct 活动金,\n" +
                "repay.tran_f6 违约金,\n" +
                "repay.tran_oi 罚息,\n" +
                "repay.tran_f3 复利,\n" +
                "repay.tran_f5 红线减免,\n" +
                "coalesce(plan.benefit_amt,0) as \"长期借款福利\"\n" +
                " from(\n" +
                " SELECT p.loan_no,p.term,ben.benefit_amt \n" +
                " FROM lcs.ln_plan_view p,\n" +
                " (\n" +
                " select tran_proc_rp_no,loan_no,repay_term from cps.intf_sdc_repay_detail where compensate_type='TCP' and state = '1'\n" +
                " union all select tran_proc_rp_no,loan_no,term repay_term from cps.intf_hhns_repay_detail where compensate_type='TCP' and state = '1'\n" +
                " union all select tran_proc_rp_no,loan_no,term repay_term from cps.intf_pfb_repay_detail where compensate_type='TCP' and state = '1'\n" +
                " ) r\n" +
                " left join lcs_rp.tr_tran_proc_rp_benefit_view ben on ben.tran_proc_rp_no = r.tran_proc_rp_no\n" +
                " WHERE  p.loan_no=r.loan_no\n" +
                " and p.term=r.repay_term\n" +
                " )plan,(\n" +
                " select  d.loan_no,p.term,sum(d.tran_amt) tran_amt,d.date_created tran_day,\n" +
                "sum(\n" +
                "case when d.tran_code in ('MPR','RPR') then d.tran_amt else 0 end\n" +
                ") tran_prin,\n" +
                "sum(\n" +
                "case when d.tran_code in ('MIN','RIN') then d.tran_amt else 0 end\n" +
                ") tran_int,\n" +
                "sum(\n" +
                "case when d.tran_code in ('ROI','MOI') then d.tran_amt else 0 end\n" +
                ") tran_oi,\n" +
                "sum(\n" +
                "case when d.tran_code in ('MF6','RF6') then d.tran_amt else 0 end\n" +
                ") tran_f6,\n" +
                "sum(\n" +
                "case when d.tran_code in ('MF5','RF5') then d.tran_amt else 0 end\n" +
                ") tran_f5,\n" +
                "sum(\n" +
                "case when d.tran_code in ('MDC','RDC') then d.tran_amt else 0 end\n" +
                ") tran_deduct,\n" +
                "sum(\n" +
                "case when d.tran_code in ('MF3','RF3') then d.tran_amt else 0 end\n" +
                ") tran_f3,\n" +
                "sum(\n" +
                "case when d.tran_code in ('MF1','RF1') then d.tran_amt else 0 end\n" +
                ") tran_f1\n" +
                " from lcs_third.tr_cust_tran_detail_view d,lcs.ln_loan_view l,lcs.ln_plan_view p\n" +
                "where d.tran_type<>'DB'\n" +
                " and p.loan_no=l.loan_no\n" +
                " and l.loan_no=d.loan_no\n" +
                " and p.plan_no=d.plan_no\n" +
                " and l.third_code in ('THIRD_SDC','THIRD_HHNS','THIRD_PFB','THIRD_HHV2')\n" +
                " and d.date_created>= current_date - 4\n" +
                " and d.date_created< current_date\n" +
                "group by d.loan_no,p.term,tran_day\n" +
                ")repay\n" +
                "left join lcs.ln_loan_view l on repay.loan_no = l.loan_no\n" +
                "left join lcs.pd_product_def pd on pd.sub_product_ver = l.sub_product_ver and pd.sub_product_code = l.sub_product_code\n" +
                "where plan.loan_no=repay.loan_no\n" +
                "and plan.term=repay.term";
        try {
            Statement statement = CCJSqlParserUtil.parse(sql);
            Assert.assertNotNull(statement);

            TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
            List<String> tableList = tablesNamesFinder.getTableList(statement);
            Assert.assertNotNull(tableList);
            String tableStr = Objects.nonNull(tableList) ? Joiner.on(",").join(tableList) : "";
            System.out.println(String.format("tables:%s", tableStr));


            Map<String, Set<String>> dbTableMap = new HashMap<>();
            for (String dbAndTable : tableList) {
                String[] splits = dbAndTable.split("\\.");
                if(splits.length == 2){
                    String db = splits[0].trim();
                    if (!dbTableMap.containsKey(db)) {
                        dbTableMap.put(db, new HashSet<>());
                    }
                    Set<String> tables = dbTableMap.get(db);
                    tables.add(splits[1].trim());
                }
            }

            for(Map.Entry<String, Set<String>> entry: dbTableMap.entrySet()){
                String dbTableStr = Joiner.on(", ").join(entry.getValue());
                System.out.println(String.format("db:%s, tables:%s", entry.getKey(), dbTableStr));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    public void test_parse_column() {

        String sql = "SELECT CASE \n" +
                "WHEN l.rpy_type = '00' THEN '等额本金'\n" +
                "WHEN l.rpy_type = '03'\n" +
                "AND l.term = 1\n" +
                "AND l.third_code = 'THIRD_NCF' THEN '1期分期'\n" +
                "WHEN l.rpy_type = '03' THEN '等本等息'\n" +
                "WHEN l.rpy_type = '02' THEN '先息后本'\n" +
                "END AS 业务模式\n" +
                ", CASE \n" +
                "WHEN pd.product_code = '360JIETIAO'\n" +
                "AND pd.cust_seg = 'X36' THEN '小额贷'\n" +
                "WHEN pd.product_code = '360JIETIAO' THEN '360借条'\n" +
                "WHEN pd.product_code = '360YINGJI' THEN '小额贷'\n" +
                "WHEN pd.product_code = '360BIG' THEN '尊享大额贷'\n" +
                "WHEN pd.product_code = '360SME' THEN '360小微贷'\n" +
                "END AS 产品类型, to_char(l.date_inst, 'yyyy-mm-dd') AS 起息日期,to_char(l.date_created, 'yyyy-mm-dd') AS 交易日期\n" +
                ", '=\"' || l.cust_no || '\"' AS 客户号\n" +
                ", '=\"' || l.contract_no || '\"' AS 合同号\n" +
                ", '=\"' || l.loan_no || '\"' AS 借据号\n" +
                ", '=\"' || l.loan_req_no || '\"' AS 借款流水号, l.term AS 借款期数\n" +
                ", l.loan_amt AS 借款本金\n" +
                ", CASE \n" +
                "WHEN l.date_inst >= '2017-11-17'\n" +
                "AND l.third_code = 'THIRD_NJ' THEN 'THIRD_NJS'\n" +
                "when l.third_code in ('THIRD_CEB','THIRD_CESZ','THIRD_CEXM','THIRD_CEBJ','THIRD_CEQH','THIRD_CECD','THIRD_CEWX') then 'THIRD_CEB'\n" +
                "ELSE l.third_code\n" +
                "END AS 合作方, l.loan_amt AS 第三方借款本金, pd.fee_rate AS 综合息费率, pt.fee2_amt AS 借款交易费, \n" +
                "'=\"' || l.pay_order_no || '\"' AS 订单号\n" +
                "FROM lcs.ln_loan_view l\n" +
                "LEFT JOIN lcs.pd_product_def pd\n" +
                "ON l.sub_product_code = pd.sub_product_code\n" +
                "AND l.sub_product_ver = pd.sub_product_ver\n" +
                "LEFT JOIN (\n" +
                "SELECT p.loan_no, SUM(fee2_amt) AS fee2_amt, SUM(fee4_amt) AS fee4_amt\n" +
                ", SUM(fee5_amt) AS fee5_amt\n" +
                "FROM lcs.ln_plan_view p\n" +
                "WHERE p.date_created >= current_date - 3\n" +
                "AND p.date_created < current_date\n" +
                "GROUP BY p.loan_no\n" +
                ") pt\n" +
                "ON pt.loan_no = l.loan_no\n" +
                "left join frsreport.data_tmp_loan_old lo on l.loan_no = lo.loan_no \n" +
                "WHERE l.date_created >= current_date - 3\n" +
                "AND l.date_created < current_date\n" +
                "AND l.third_code NOT IN ('THIRD_JX','THIRD_TJB','THIRD_GSB','THIRD_HYXJ','THIRD_XFV2','THIRD_JYJ','THIRD_WJB','THIRD_NJUN','THIRD_HXSZ','THIRD_ICGS','THIRD_JXB','THIRD_XJHH','THIRD_SD'\n" +
                ",'THIRD_QHB','THIRD_WJXW','THIRD_WK','THIRD_WFB','THIRD_SDC','THIRD_ZRXT','THIRD_LYB','THIRD_BHXT','THIRD_CEB','THIRD_CESZ','THIRD_CEXM','THIRD_CEBJ','THIRD_CEQH','THIRD_CECD','THIRD_CEWX','THIRD_TJNS','THIRD_ZHXT','THIRD_GDXT','THIRD_BXB','THIRD_ZYXJ','THIRD_CJXW'\n" +
                ",'THIRD_HNXT','THIRD_WHZB','THIRD_CAB','THIRD_YTB','THIRD_SZSB','THIRD_YXTP','THIRD_KSNS','THIRD_TJBH','THIRD_XFV3','THIRD_FMB','THIRD_KLB','THIRD_HAXJ','THIRD_WHB','THIRD_HXB','THIRD_MSXJ'\n" +
                ",'THIRD_GLB','THIRD_YXV2','THIRD_SCXT','THIRD_JJB','THIRD_SHNS','THIRD_HTB','THIRD_ICJX','THIRD_YCXT','THIRD_HXXT','THIRD_QDB','THIRD_JSXT','THIRD_HHNS','THIRD_ZHFR','THIRD_HHB','THIRD_TBHS','THIRD_QLB'\n" +
                ",'THIRD_WMXT','THIRD_ICSC','THIRD_PFB','THIRD_HHNS','THIRD_YXFR','THIRD_TSB','THIRD_XDPB','THIRD_HEXJ','THIRD_YXNJ','THIRD_SZNS','THIRD_SZXW','THIRD_GZB')\n" +
                "and lo.loan_no is not null\n" +
                "AND (l.third_code != 'THIRD_NCF' or (l.third_code = 'THIRD_NCF' and l.date_inst < to_date('2018-10-26','yyyy-mm-dd')))\n" +
                "AND (l.third_code != 'THIRD_CMBC' or (l.third_code = 'THIRD_CMBC' and l.date_inst < to_date('2018-12-06','yyyy-mm-dd')))\n" +
                "ORDER BY l.date_created, l.cust_no,l.third_code";
        try {
            Statement statement = CCJSqlParserUtil.parse(sql);
            Assert.assertNotNull(statement);

            TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
            List<String> tableList = tablesNamesFinder.getTableList(statement);
            Assert.assertNotNull(tableList);
            String tableStr = Objects.nonNull(tableList) ? Joiner.on(",").join(tableList) : "";
            System.out.println(String.format("tables:%s", tableStr));


            Map<String, Set<String>> dbTableMap = new HashMap<>();
            for (String dbAndTable : tableList) {
                String[] splits = dbAndTable.split("\\.");
                if(splits.length == 2){
                    String db = splits[0].trim();
                    if (!dbTableMap.containsKey(db)) {
                        dbTableMap.put(db, new HashSet<>());
                    }
                    Set<String> tables = dbTableMap.get(db);
                    tables.add(splits[1].trim());
                }
            }

            for(Map.Entry<String, Set<String>> entry: dbTableMap.entrySet()){
                String dbTableStr = Joiner.on(", ").join(entry.getValue());
                System.out.println(String.format("db:%s, tables:%s", entry.getKey(), dbTableStr));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}
